﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class D_BGM_ProjectOperationReport
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //string strGHRMS_DB_Name = ConfigurationSettings.AppSettings["GHRMS.DB.Name"].ToString();

        public D_BGM_ProjectOperationReport()
        {

        }

        public DataTable GetFull(string bgm_proj_oper_seq
            , string bgm_proj_oper_exp_seq, string rpt_type)
        {
            string strSQL = "";
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@rpt_type", rpt_type);

                switch (rpt_type)
                {
                    case "4, 5":
                        strSQL = @"
SELECT sum(coalesce(ppri.Amount, 0)) as amount
, ppri.PprId as id, null as id2, ppr.Code as code, ppr.Name as name, ppr.RequestDate as tdate
FROM IcmPrePurchaseRequestItem ppri
inner JOIN IcmPrePurchaseRequest ppr on ppri.PprId = ppr.PprId
inner JOIN bgm_project_operation_expense opexp ON ppri.ProjOperExpId = opexp.bgm_proj_oper_exp_seq
left join IcmPurchaseRequestItem pri on ppri.PrItemId = pri.PrItemId
left join IcmPurchaseRequest pr on pri.PrId = pr.PrId
	and pr.RecordStatus = 'A'
WHERE ppr.RecordStatus = 'A'
and pr.PrId is null
and opexp.bgm_proj_oper_seq = @bgm_proj_oper_seq
group by ppri.PprId, ppr.Code, ppr.Name, ppr.RequestDate

union all

select sum(coalesce(q.NetAmount, 0) / q.Qty * coalesce(ppri.Qty, 0)) as amount
, pr.PrId as id, null as id2, pr.Code as code, pr.Name as name, pr.PurchaseDate as tdate
from IcmPrePurchaseRequestItem ppri
inner JOIN bgm_project_operation_expense opexp ON ppri.ProjOperExpId = opexp.bgm_proj_oper_exp_seq
--inner JOIN bgm_project_operation_report rpt ON ppri.PprItemId = rpt.icm_ppr_item_seq
    --and rpt.record_status <> 'D'
inner join IcmPurchaseRequestItem pri on ppri.PrItemId = pri.PrItemId
inner join IcmPurchaseRequest pr on pri.PrId = pr.PrId and pr.RecordStatus = 'A'
inner join IcmBidderQuotation q on pri.PrItemId = q.PrItemId
where opexp.bgm_proj_oper_seq = @bgm_proj_oper_seq

group by pr.PrId, pr.Code, pr.Name, pr.PurchaseDate
order by tdate";
                        break;
                    case "6":
                        strSQL = @"
select sum(coalesce(mrd.amount, 0)) as amount
, mr.money_req_seq as id, p.ref_no as code2, mr.ref_no as code, mr.money_req_name as name, mr.money_req_date as tdate
from fim_money_requests mr
inner join fim_money_request_dtls mrd on mr.money_req_seq = mrd.money_req_seq
inner join bgm_project_operation_expense opexp ON mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq
left join fim_payments p on mr.money_req_seq = p.money_req_seq
	and p.record_status = 'A'
where mr.record_status = 'A'
and opexp.bgm_proj_oper_seq = @bgm_proj_oper_seq
group by mr.money_req_seq, p.ref_no, mr.ref_no, mr.money_req_name, mr.money_req_date
order by tdate";
                        break;
                    case "7":
                        strSQL = @"
select distinct coalesce(p.amount, 0) + coalesce(p.tax, 0) as amount
, p.pay_seq as id, mr.ref_no as code2, p.ref_no as code, p.pay_name as name, p.pay_date as tdate
from fim_payments p
inner join fim_money_request_dtls mrd on p.money_req_seq = mrd.money_req_seq
inner join bgm_project_operation_expense opexp ON mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq
left join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
	and mr.record_status = 'A'
where p.record_status = 'A'
and opexp.bgm_proj_oper_seq = @bgm_proj_oper_seq
order by tdate";
                        break;
                    case "8":
                        strSQL = @"
select coalesce(r.amount, 0) as amount
, r.receive_seq as id, mr.ref_no as code2, r.ref_no as code, concat('รับเงินเลขที่ใบเสร็จ ', r.receipt_no) as name, r.receive_date as tdate
from fim_receives r
left join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
where r.record_status <> 'D'
and mr.bgm_proj_oper_seq = @bgm_proj_oper_seq
order by tdate";
                        break;
                }

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public IList<M_BGM_ProjectOperationReport> GetList(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_proj_oper_exp_seq, Nullable<int> rpt_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationReport> infoList = new List<M_BGM_ProjectOperationReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@rpt_type", rpt_type);

                strSQL = "SELECT rpt.bgm_proj_oper_exp_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as cal_amount"

                    + " FROM bgm_project_operation_report rpt"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " WHERE rpt.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " and opexp.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_proj_oper_exp_seq != null ? " and rpt.bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq" : "")
                    + (rpt_type != null ? " and rpt.rpt_type = @rpt_type" : "")
                    + " ORDER BY rpt.bgm_proj_exp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationReport info = new M_BGM_ProjectOperationReport();
                        info.bgm_proj_oper_exp_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationReport> GetCalculateList(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_proj_oper_exp_seq, Nullable<int> rpt_type
            , Nullable<int> bgm_trans_from_seq, Nullable<int> bgm_trans_to_seq
            , Nullable<int> bgm_booking_seq, Nullable<int> icm_ppr_item_seq
            , Nullable<int> icm_pr_item_seq, Nullable<int> icm_po_item_seq
            , Nullable<int> icm_dn_item_seq, Nullable<int> money_req_dtls_seq
            , Nullable<int> return_dtls_seq, Nullable<int> petition_dtls_seq
            , Nullable<int> pay_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationReport> infoList = new List<M_BGM_ProjectOperationReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@rpt_type", rpt_type);
                dbManager.AddParameters(4, "@bgm_trans_from_seq", bgm_trans_from_seq);
                dbManager.AddParameters(5, "@bgm_trans_to_seq", bgm_trans_to_seq);
                dbManager.AddParameters(6, "@bgm_booking_seq", bgm_booking_seq);
                dbManager.AddParameters(7, "@icm_ppr_item_seq", icm_ppr_item_seq);
                dbManager.AddParameters(8, "@icm_pr_item_seq", icm_pr_item_seq);
                dbManager.AddParameters(9, "@icm_po_item_seq", icm_po_item_seq);
                dbManager.AddParameters(10, "@icm_dn_item_seq", icm_dn_item_seq);
                dbManager.AddParameters(11, "@money_req_dtls_seq", money_req_dtls_seq);
                dbManager.AddParameters(12, "@return_dtls_seq", return_dtls_seq);
                dbManager.AddParameters(13, "@petition_dtls_seq", petition_dtls_seq);
                dbManager.AddParameters(14, "@pay_seq", pay_seq);

                strSQL = "SELECT rpt.bgm_proj_oper_exp_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as cal_amount"

                    + " FROM bgm_project_operation_report rpt"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " WHERE rpt.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " and rpt.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_proj_oper_exp_seq != null ? " and rpt.bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq" : "")
                    + (rpt_type != null ? " and rpt_type = @rpt_type" : "")
                    + (bgm_trans_from_seq != null ? " and coalesce(bgm_trans_from_seq, -1) not like @bgm_trans_from_seq" : "")
                    + (bgm_trans_to_seq != null ? " and coalesce(bgm_trans_to_seq, -1) not like @bgm_trans_to_seq" : "")
                    + (bgm_booking_seq != null ? " and coalesce(bgm_booking_seq, -1) not like @bgm_booking_seq" : "")
                    + (icm_ppr_item_seq != null ? " and coalesce(icm_ppr_item_seq, -1) not like @icm_ppr_item_seq" : "")
                    + (icm_pr_item_seq != null ? " and coalesce(icm_pr_item_seq, -1) not like @icm_pr_item_seq" : "")
                    + (icm_po_item_seq != null ? " and coalesce(icm_po_item_seq, -1) not like @icm_po_item_seq" : "")
                    + (icm_dn_item_seq != null ? " and coalesce(icm_dn_item_seq, -1) not like @icm_dn_item_seq" : "")
                    + (money_req_dtls_seq != null ? " and coalesce(money_req_dtls_seq, -1) not like @money_req_dtls_seq" : "")
                    + (return_dtls_seq != null ? " and coalesce(return_dtls_seq, -1) not like @return_dtls_seq" : "")
                    + (petition_dtls_seq != null ? " and coalesce(petition_dtls_seq, -1) not like @petition_dtls_seq" : "")
                    + (pay_seq != null ? " and coalesce(pay_seq, -1) not like @pay_seq" : "")
                    + " ORDER BY bgm_proj_exp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationReport info = new M_BGM_ProjectOperationReport();
                        info.bgm_proj_oper_exp_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_BGM_ProjectOperationReport GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_BGM_ProjectOperationReport info = new M_BGM_ProjectOperationReport();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT rpt.bgm_proj_oper_rpt_seq, rpt.bgm_proj_oper_exp_seq, rpt.bgm_trans_from_seq"
                    + ", rpt.bgm_trans_to_seq, rpt.icm_dn_item_seq, rpt.money_req_dtls_seq"
                    + ", rpt.return_dtls_seq, rpt.petition_dtls_seq, rpt.pay_seq"
                    + ", rpt.used_amount, rpt.cal_amount, rpt.rpt_type"

                    + " FROM bgm_project_operation_report rpt"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " WHERE rpt.bgm_proj_oper_rpt_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bgm_proj_oper_rpt_seq = Convert.ToInt32(drc[0]["bgm_proj_oper_rpt_seq"]);
                    info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_exp_seq"]);
                    info.bgm_trans_from_seq = cn.ConvertNullInt(drc[0]["bgm_trans_from_seq"]);
                    info.bgm_trans_to_seq = cn.ConvertNullInt(drc[0]["bgm_trans_to_seq"]);
                    info.icm_dn_item_seq = cn.ConvertNullInt(drc[0]["icm_dn_item_seq"]);
                    info.money_req_dtls_seq = cn.ConvertNullInt(drc[0]["money_req_dtls_seq"]);
                    info.return_dtls_seq = cn.ConvertNullInt(drc[0]["return_dtls_seq"]);
                    info.petition_dtls_seq = cn.ConvertNullInt(drc[0]["petition_dtls_seq"]);
                    info.pay_seq = cn.ConvertNullInt(drc[0]["pay_seq"]);
                    info.used_amount = cn.ConvertNullDouble(drc[0]["used_amount"]);
                    info.cal_amount = cn.ConvertNullDouble(drc[0]["cal_amount"]);
                    info.rpt_type = cn.ConvertNullInt(drc[0]["rpt_type"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(M_BGM_ProjectOperationReport info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@bgm_proj_oper_rpt_seq", info.bgm_proj_oper_rpt_seq);
                dbManager.AddParameters(1, "@descr", info.descr);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@bgm_trans_from_seq", info.bgm_trans_from_seq);
                dbManager.AddParameters(4, "@bgm_trans_to_seq", info.bgm_trans_to_seq);
                dbManager.AddParameters(5, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(6, "@return_dtls_seq", info.return_dtls_seq);
                dbManager.AddParameters(7, "@petition_dtls_seq", info.petition_dtls_seq);
                dbManager.AddParameters(8, "@pay_seq", info.pay_seq);

                dbManager.AddParameters(9, "@used_amount", info.used_amount);
                dbManager.AddParameters(10, "@cal_amount", info.cal_amount);
                dbManager.AddParameters(11, "@rpt_type", info.rpt_type);

                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@created_by", info.created_by);
                dbManager.AddParameters(14, "@created_when", info.created_when);

                strSQL = "INSERT INTO bgm_project_operation_report"
                    + "(descr, bgm_proj_oper_exp_seq, bgm_trans_from_seq, bgm_trans_to_seq"
                        + ", money_req_dtls_seq, return_dtls_seq, petition_dtls_seq, pay_seq"
                        + ", used_amount, cal_amount, rpt_type"
                        + ", record_status, created_by, created_when)"
                    + " VALUES (@descr, @bgm_proj_oper_exp_seq, @bgm_trans_from_seq, @bgm_trans_to_seq"
                        + ", @money_req_dtls_seq, @return_dtls_seq, @petition_dtls_seq, @pay_seq"
                        + ", @used_amount, @cal_amount, @rpt_type"
                        + ", @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_BGM_ProjectOperationReport info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@bgm_proj_oper_rpt_seq", info.bgm_proj_oper_rpt_seq);
                dbManager.AddParameters(1, "@descr", info.descr);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@bgm_trans_from_seq", info.bgm_trans_from_seq);
                dbManager.AddParameters(4, "@bgm_trans_to_seq", info.bgm_trans_to_seq);
                dbManager.AddParameters(5, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(6, "@return_dtls_seq", info.return_dtls_seq);
                dbManager.AddParameters(7, "@petition_dtls_seq", info.petition_dtls_seq);
                dbManager.AddParameters(8, "@pay_seq", info.pay_seq);

                dbManager.AddParameters(9, "@updated_by", info.updated_by);
                dbManager.AddParameters(10, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation_report"
                    + " set descr = @descr"
                    + ", bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq"
                    + ", bgm_trans_from_seq = @bgm_trans_from_seq"
                    + ", bgm_trans_to_seq = @bgm_trans_to_seq"
                    + ", money_req_dtls_seq = @money_req_dtls_seq"
                    + ", return_dtls_seq = @return_dtls_seq"
                    + ", petition_dtls_seq = @petition_dtls_seq"
                    + ", pay_seq = @pay_seq"
                    + ", used_amount = @used_amount"
                    + ", cal_amount = @cal_amount"
                    + ", rpt_type = @rpt_type"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_rpt_seq = @bgm_proj_oper_rpt_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_BGM_ProjectOperationReport info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@bgm_proj_oper_rpt_seq", info.bgm_proj_oper_rpt_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation_report"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_rpt_seq = @bgm_proj_oper_rpt_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateStatus(M_BGM_ProjectOperationReport info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.bgm_proj_oper_rpt_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation_report"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_rpt_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
